import openpyxl
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
from sklearn.metrics import cohen_kappa_score
import matplotlib
matplotlib.rcParams['font.family'] = ['SimHei']  # 使用中文黑体
matplotlib.rcParams['axes.unicode_minus'] = False  # 解决负号显示问题
# 读取数据
df = pd.read_excel(r'D:\AES code\EA\Exp_Data\实验数据-详细分数(detailed).xlsx')


# 按论文类型分组
essay_types = df['Type_of_essay'].unique()

results = []
for essay_type in essay_types:
    type_df = df[df['Type_of_essay'] == essay_type]

    # 计算human_score与base_score的Cohen's Kappa系数
    human_base_kappa = cohen_kappa_score(
        type_df['human_score'],
        type_df['base_score'],
        weights='quadratic'
    )

    # 计算human_score与evolved_score的Cohen's Kappa系数
    human_evolved_kappa = cohen_kappa_score(
        type_df['human_score'],
        type_df['evolved_score'],
        weights='quadratic'
    )

    results.append({
        'Type_of_essay': essay_type,
        '数量': len(type_df),
        'Human-Base Kappa': human_base_kappa,
        'Human-Evolved Kappa': human_evolved_kappa
    })

# 创建结果DataFrame
results_df = pd.DataFrame(results)
print(results_df)

# 将结果导出为Excel文件
output_file = '/type_compare_Results\kappa值比较（作文类别）\kappa_results.xlsx'
with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
    results_df.to_excel(writer, sheet_name='Kappa系数', index=False)

    # 创建可视化图表并保存到Excel
    workbook = writer.book

    # 添加条形图工作表
    plt.figure(figsize=(12, 6))

    # 准备绘图数据
    plot_data = results_df.melt(
        id_vars=['Type_of_essay', '数量'],
        value_vars=['Human-Base Kappa', 'Human-Evolved Kappa'],
        var_name='比较类型',
        value_name='Kappa值'
    )

    # 绘制分组条形图
    ax = sns.barplot(x='Type_of_essay', y='Kappa值', hue='比较类型', data=plot_data)
    plt.title('不同论文类型的Cohen\'s Kappa系数比较')
    plt.xlabel('论文类型')
    plt.ylabel('Kappa系数值')
    plt.tight_layout()

    # 保存图表到本地文件
    bar_chart_path = '/type_compare_Results\kappa值比较（作文类别）\kappa_bar_chart.png'
    plt.savefig(bar_chart_path)

    # 创建新的工作表来放置图表
    worksheet = workbook.create_sheet('条形图')
    img = openpyxl.drawing.image.Image(bar_chart_path)
    worksheet.add_image(img, 'A1')

    # 创建热力图
    plt.figure(figsize=(10, 6))
    pivot_data = results_df.set_index('Type_of_essay')[['Human-Base Kappa', 'Human-Evolved Kappa']]
    sns.heatmap(pivot_data, annot=True, cmap='YlGnBu', fmt='.3f', linewidths=.5)
    plt.title('不同论文类型的Kappa系数热力图')
    plt.tight_layout()

    # 保存热力图
    heatmap_path = '/type_compare_Results\kappa值比较（作文类别）\kappa_heatmap.png'
    plt.savefig(heatmap_path)

    # 添加热力图到Excel
    worksheet = workbook.create_sheet('热力图')
    img = openpyxl.drawing.image.Image(heatmap_path)
    worksheet.add_image(img, 'A1')

print(f"结果已保存到: {output_file}")
print(f"条形图已保存到: {bar_chart_path}")
print(f"热力图已保存到: {heatmap_path}")

# 显示图表
plt.show()